package com.hy.dao;

import com.hy.bean.Comment;
import com.hy.bean.TableLike;
import com.hy.utils.DBManager;
import com.hy.utils.DateUtil;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.util.List;
import java.util.Map;

public class CardDao {
    private QueryRunner qr = new QueryRunner();

    public List<Map<String,Object>> selectCard(Integer limit,String title) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select c.*,u.user_name,u.img from card c INNER JOIN ums_user u on u.id = c.user_id and title like ? and c.tombstone='0'  limit 0,?";
        List<Map<String,Object>> query = qr.query(conn, sql,new MapListHandler(),'%'+title+'%',limit);
        DbUtils.close(conn);
        return query;
    }
    public List<Map<String,Object>> selectComment(Integer id) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select c.id,c.comment_content,c.parent_id,c.user_id,u.user_name,u.img,c.create_time,u.address from `comment` c, card ca,ums_user u where\n" +
                " c.card_id=ca.id and c.user_id=u.id  and c.card_id = ? and c.tombstone='0' ";
        List<Map<String,Object>> query = qr.query(conn, sql,new MapListHandler(),id);
        DbUtils.close(conn);
        return query;
    }

    public List<Map<String,Object>> selectFollow(Integer id, Integer limit) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select c.*,u.img,u.user_name from card c INNER JOIN ums_user u on c.user_id = u.id where user_id in \n" +
                "(select f.user1_id from follow f INNER JOIN ums_user u on f.user_id = u.id \n" +
                "INNER JOIN ums_user u1 on f.user1_id = u1.id where u.id = ? and f.tombstone='0') and c.user_id <> ? and  c.tombstone='0' and u.tombstone='0' limit 0,?";
        List<Map<String,Object>> query = qr.query(conn, sql, new MapListHandler(), id,id,limit);
        DbUtils.close(conn);
        return query;
    }
    public List<Map<String,Object>> selectCommentParentId(Integer id, Integer cardId) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select c.id,c.comment_content,c.parent_id,c.user_id,u.user_name,u.img,c.create_time,u.address " +
                "from `comment` c, card ca,ums_user u where\n" +
              " c.card_id=ca.id and c.user_id=u.id and c.parent_id=? and c.card_id = ? ";
        List<Map<String,Object>> query = qr.query(conn, sql, new MapListHandler(), id,cardId);
        DbUtils.close(conn);
        return query;
    }


    public List<Map<String,Object>> selectRecommend(Integer id,Integer limit) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select c.*,u.user_name,u.img from card c INNER JOIN " +
                "ums_user u on u.id = c.user_id where c.id not in " +
                "(select card_id from table_like where create_id = ?  " +
                "and tombstone = '0') and c.tombstone = '0'  and u.tombstone = '0' and c.user_id <> ? " +
                "ORDER BY recommend DESC limit 0,?";



        List<Map<String, Object>> query = qr.query(conn, sql, new MapListHandler(), id,id,limit);


        DbUtils.close(conn);
        return query;
    }
    public boolean insertComment(Comment comment) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "INSERT INTO comment  VALUES (null, ?, ?, ?, ?, ?, NULL, ?, NULL, '0');";
        int count = qr.update(conn, sql,comment.getCommentContent(),comment.getCardId(),comment.getParentId(),comment.getUserId(),DateUtil.datetime(),comment.getCreateId());
        DbUtils.close(conn);

        return count > 0 ? true : false;
    }
    public Long selectCardCount(Integer id) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select count(1) from card c INNER JOIN ums_user u on u.id = c.user_id where c.user_id <> ?";
        Long query = qr.query(conn, sql, new ScalarHandler<Long>(),id);
        DbUtils.close(conn);
        return query;
    }
    public Long selectCommentCount(Integer id) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select count(*) from comment where card_id = ?";
        Long query = qr.query(conn, sql, new ScalarHandler<Long>(),id);
        DbUtils.close(conn);
        return query;
    }

    //查询赞同数
    public Long selectEndorseCount(Integer cardId) throws Exception {
        Connection conn = DBManager.getConn();

        String sql = "select count(*) from table_like where " +
                "card_id = ? and endorse_status = '1'  and tombstone = '0'";
        Long count = qr.query(conn, sql, new ScalarHandler<>(), cardId);

        DbUtils.close(conn);

        return count;
    }

    //查询有没有这条数据
    public boolean selectTabLike(Integer cardId,Integer userId) throws Exception {
        Connection conn = DBManager.getConn();

        String sql = "select count(*) from table_like where card_id = ? and uid = ? and tombstone = '0'";
        long count = qr.query(conn, sql, new ScalarHandler<>(), cardId,userId);

        DbUtils.close(conn);

        return count > 0 ? true : false;
    }

    //添加当前用户对帖子的操作
    public boolean insertTabLike(Integer cardId,Integer userId
            ,String endorseStatus,String collectStatus,String likeStatus) throws Exception {
        Connection conn = DBManager.getConn();

        String sql = "insert into table_like(uid,card_id,endorse_status,collect_status" +
                ",like_status,create_id,create_time) value(?,?,?,?,?,?,?)";
        int count = qr.update(conn, sql,userId, cardId,endorseStatus,collectStatus,likeStatus
                ,userId, DateUtil.datetime());

        DbUtils.close(conn);

        return count > 0 ? true : false;
    }

    //赞同修改
    public boolean updateEndorse(Integer cardId,Integer userId,String marker) throws Exception {
        Connection conn = DBManager.getConn();

        String sql = "update table_like set endorse_status = ?,update_id = ?,update_time = ? where uid = ? and card_id = ?";
        int count = qr.update(conn, sql, marker, userId,DateUtil.datetime(),userId, cardId);

        DbUtils.close(conn);

        return count > 0 ? true : false;
    }
    //反对修改
    public boolean updateOppose(Integer cardId,Integer userId,String marker) throws Exception {
        Connection conn = DBManager.getConn();

        String sql = "update table_like set endorse_status = ?,update_id = ?,update_time = ? where uid = ? and card_id = ?";
        int count = qr.update(conn, sql, marker, userId,DateUtil.datetime(),userId, cardId);

        DbUtils.close(conn);

        return count > 0 ? true : false;
    }
    //收藏修改
    public boolean updateCollect(Integer cardId,Integer userId,String marker) throws Exception {
        Connection conn = DBManager.getConn();

        String sql = "update table_like set collect_status = ?,update_id = ?,update_time = ? where uid = ? and card_id = ?";
        int count = qr.update(conn, sql, marker, userId,DateUtil.datetime(),userId, cardId);

        DbUtils.close(conn);

        return count > 0 ? true : false;
    }
    //喜欢修改
    public boolean updateLove(Integer cardId,Integer userId,String marker) throws Exception {
        Connection conn = DBManager.getConn();

        String sql = "update table_like set like_status = ?,update_id = ?,update_time = ? where uid = ? and card_id = ?";
        int count = qr.update(conn, sql, marker, userId,DateUtil.datetime(),userId, cardId);

        DbUtils.close(conn);

        return count > 0 ? true : false;
    }

    public TableLike selectTableLikeByCardId(Integer cardId, Integer uid) throws Exception {
        Connection conn = DBManager.getConn();
        BasicRowProcessor brp = new BasicRowProcessor(new GenerousBeanProcessor());

        String sql = "select * from table_like where " +
                "card_id = ? and uid = ? and tombstone = '0'";
        TableLike tableLike = qr.query(conn, sql, new BeanHandler<>(TableLike.class,brp), cardId,uid);

        DbUtils.close(conn);

        return tableLike;
    }


    /*public List<Map<String,Object>> selectComment(Integer id) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select cm.*,u.user_name,u.img from card c INNER JOIN `comment` cm on c.id = cm.card_id INNER JOIN ums_user u on u.id = cm.user_id where c.id = ?";
        List<Map<String, Object>> query = qr.query(conn, sql, new MapListHandler(), id);
        DbUtils.close(conn);
        return query;
    }*/
    public static void main(String[] args) throws Exception {
        CardDao cardDao = new CardDao();
        System.out.println(cardDao.selectComment(1).toString());
    }
}
